import datetime
import joblib
import numpy as py 
import pymysql
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump
import requests
# from sklearn import pymrmr

class Tianqi(object):
    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]

        self.url = 'http://v1.yiketianqi.com/api'

    def get_data(self):
        """获取天气数据"""
        data_list = []
        for d in self.date_list:
            cont = {
                'appid': '13871882',  # 使用自己注册的appid   #是   string  用户appid  注册开发账号
                'appsecret': 'PW4KZrCI',  # 是   string  用户appsecret
                'version': 'history',  # 是   string  接口版本标识  固定值：History 每个接口的version值都不一样
                'year': d[:4],  # 是   string  年份  如：2015
                'month': d[5:7],  # 是   string  月份  如：5
                # "cityid"  #否   string  城市ID  请参考 城市ID列表
                'city': '南昌'  # 否 string  城市名称  不要带市和区：如：青岛、扶西
            }
            # 发起请求获取数据
            res = requests.get(self.url + '?', params=cont)
            res_data = res.json()
            print(res_data)
            for i in res_data['data']:
                data_list.append({
                    "date": datetime.datetime.strptime(i["ymd"], "%Y-%m-%d"),
                    "bWendu": i["bWendu"],
                    "yWendu": i["yWendu"],
                    "tianqi": i["tianqi"],
                    "fengli": i["fengli"],
                    })
                
            
        df = pd.DataFrame(data_list)
        df.to_csv('data-mining-experiment-220301/zy7/weather.csv')

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='ljy',
            port=3306,
            charset='utf8'
        )
        self.weather_data = pd.read_csv("data-mining-experiment-220301\zy7\weather.csv")
        
    def is_holiday(self,date):
        """判断是否为节假日"""
        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2024-01-02', '2024-01-03']:
            return 1
        return 0
        
    def get_data(self):
            cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
            sql = """
            SELECT DATE(g.create_time) as date, count(*) as count FROM order_user_gate_rel g WHERE DATE(g.create_time) < '2025-01-01' GROUP BY date
            """
            cursor.execute(sql)
            ret = cursor.fetchall()
            df = pd.DataFrame(ret)
            
            # 合并天气数据
            self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])
            df['date'] = pd.to_datetime(df['date'])
            df_pivot = df.merge(self.weather_data, on='date')
            
            df_pivot.set_index('date', inplace=True)

            df_pivot['dow'] = df_pivot.index.dayofweek
            df_pivot['month'] = df_pivot.index.month
            df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)

            #独热编码
            df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)

            #归一化入园数
            scaler = MinMaxScaler()
            features = df_pivot[['count']]
            df_pivot['count'] = scaler.fit_transform(features)
            joblib.dump(scaler, 'data-mining-experiment-220301/zy7/scaler.joblib')

            df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('°', '').astype(int)
            df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('°', '').astype(int)

            weather_features = df_pivot[['bWendu', 'yWendu']]
            df_pivot[['bWendu', 'yWendu']] = scaler.fit_transform(weather_features)
            joblib.dump(scaler, 'data-mining-experiment-220301/zy7/weather_scaler.joblib')

            print(df_pivot.head)

            df_pivot.to_csv('data-mining-experiment-220301/zy7/scenic_data.csv', index=False)
           
            
if __name__ == '__main__':
    tian = Tianqi()
    tian.get_data()
    mu = MysqlUtils()
    mu.get_data()

         # print(df.head)
            # #转换格式
            # date_range = pd.date_range(start="2024-07-01", end="2025-03-02", freq="D")
            # hours = range(6,24)
            # full_index = pd.MultiIndex.from_product([date_range, hours], names=['date','hour'])
            # df_full = df .set_index(['date','hour']).reindex(full_index, fill_value=0).reset_index()
            # #按天组织数据，每行包含18个小时的检票次数
            # df_pivot = df_full.pivot(index='date', columns='hour', values='count')
            # # print(df_pivot.head)
            # df_pivot['dow'] = df_pivot.index.dayofweek
            # df_pivot['month'] = df_pivot.index.month
            # df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
            
            # # 对星期几和月份进行独热编码
            # df_pivot = pd.get_dummies(df_pivot, columns=['dow','month'], dtype=int)
            # # print(df_pivot.head())
            # # 归一化小时列
            # hours_columns = list(range(6, 24))
            # df_hours = df_pivot[hours_columns].copy()
            # feature_columns = [col for col in df_pivot.columns if col not in hours_columns]
            # df_feature = df_pivot[feature_columns].copy()
            # scaler = MinMaxScaler()
            # scaler_hours = scaler.fit_transform(df_hours)
            # dump(scaler,"data-mining-experiment/zy5/scaler.joblib")
            # # 将归一化后的数据转换为DataFrame
            # df_hours_scaled = pd.DataFrame(scaler_hours, columns=hours_columns, index=df_hours.index)
            # # 合并
            # df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
            # print(df_pivot_clean)
            # df_pivot_clean.to_csv(r"C:\Program Files\Git\data-mining-experiment-220301\zy7\scenic_data.csv",index=False)